package tsli.acm.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;

public class GACommissionNegativeDAO {
	private Database db;
    

	 public GACommissionNegativeDAO(Database db) {
		 this.db = db;
	 }
	 
	 public List<Map<String, Object>> getAllAgentStructure() {
			String vaSql = "SELECT * "
					+ "FROM TSLI_SFC_COMMISSION_NEGATIVE";
			List<Map<String, Object>> result = db.queryList(vaSql);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
		}
	 
	 public List<Map<String, Object>> getFreeLookDetails(String paPeNo,String paYYMM) {
			String vaSql = ""+
					"	select p.PE_NO_COL,fl.PO_NO,fl.RIDER_CD											\n"+
					"	,SUBSTR(p.CLOS_YM,5,2)||'/'||SUBSTR(p.CLOS_YM,1,4) as ISSUE_DATE    			\n"+
					"	,(p.AFYP+p.ARYP2) as TOTAL_FREELOOK	                                			\n"+
					"	from TSLI_TEMP_AL_FREELOOK fl                                       			\n"+
					"	LEFT OUTER JOIN TSLI_GA_COMMISSION_POSITIVE p                       			\n"+
					"	ON fl.PE_NO_SM = p.PE_NO_SM                                         			\n"+
					"	and fl.RIDER_CD = p.RIDER_CD                                        			\n"+
					"	and fl.PO_NO = p.PO_NO                                              			\n"+
					"	and p.CLOS_YM <=  "+paYYMM+"													\n"+		
					"	where fl.FREELOOK_YM = "+paYYMM+"                                   			\n"+
					"	and fl.PE_NO_SM = "+paPeNo+"                                        			\n"+
					"	--Group because it duplicate                                       			 	\n"+
					"	group by  p.PE_NO_COL,fl.PE_NO_SM,fl.PO_NO,fl.RIDER_CD,(p.AFYP+p.ARYP2)         \n"+
					"	,SUBSTR(p.CLOS_YM,5,2)||'/'||SUBSTR(p.CLOS_YM,1,4)                  			\n"+
					"	order by fl.PO_NO,fl.RIDER_CD                                       			\n";
					
//					"select n.PO_NO,n.RIDER_CD, p.CLOS_YM as ISSUE_DATE,(n.AFYP+n.ARYP2) as TOTAL_FREELOOK "
//					+ " FROM TSLI_GA_COMMISSION_NEGATIVE n,TSLI_GA_COMMISSION_POSITIVE p "
//					+ " where n.PO_NO = p.PO_NO "
//					+ " and n.PE_NO_SM = "+paPeNo+" "
//					+ " and p.CLOS_YM <= "+paYYMM+" "
//					+ " and p.DTY_CD in ('S10240206','S10240203','S10240205') "
//					+ " and p.CLOS_YM = ( "
//					+ " select min(CLOS_YM) "
//					+ " from TSLI_GA_COMMISSION_POSITIVE a "
//					+ " where a.PO_NO = p.PO_NO "
//					+ " ) "
//					+ " group by n.PE_NO_SM,n.PO_NO,p.CLOS_YM,(n.AFYP+n.ARYP2),n.RIDER_CD "
//					+ " order by n.PE_NO_SM,n.PO_NO";
//					
//			System.out.println("getFreeLookDetails : \n"+vaSql);
			
			List<Map<String, Object>> result = db.queryList(vaSql);
	        
	        if (result != null && result.size() > 0) {
	            return result; 
	        } else {
	            return null;
	        }
		}
	 
	 
	 public Map<String, Object> getAVPFreeLook(String paPeNo , String paYear) {
			String vaSql = "SELECT ryc.SUM_RYC AS RYC"
					+ " FROM(SELECT sum(NVL(commission_negative.ryc,0)) AS SUM_RYC"
					+ "      FROM TSLI_GA_COMMISSION_negative commission_negative"
					+ "      WHERE 	commission_negative.pe_no_mm = ?"
					+ "              AND ( commission_negative.cont_mms BETWEEN 13 and 72 )"
					+ "              AND commission_negative.pe_no_soli = commission_negative.pe_no_col "
					+ "              AND TO_DATE(commission_negative.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')"
					+ "      GROUP BY commission_negative.pe_no_mm"
					+ "      UNION ALL"
					+ "      SELECT 0.5*sum(NVL(commission_negative.ryc,0))  AS SUM_RYC"
					+ "      FROM TSLI_GA_COMMISSION_negative commission_negative"
					+ "      WHERE 	commission_negative.pe_no_mm = ?"
					+ "              AND ( commission_negative.cont_mms BETWEEN 13 and 72 )"
					+ "              AND commission_negative.pe_no_soli <> commission_negative.pe_no_col"
					+ "              AND TO_DATE(commission_negative.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')"
					+ "      GROUP BY commission_negative.pe_no_mm) ryc";
			Map<String, Object> result = db.querySingle(vaSql,paPeNo,paPeNo);
		    if (result != null && result.size() > 0) {
		        return result;
		    } else {
		        return null;
		    }
		}
	 
	 
	 
	 public List<Map<String, Object>> getAVPFreeLookDetail(String paPeNo , String paYear) {
			String vaSql = "SELECT commission_negative.CLOS_YM,commission_negative.PE_NO_COL,commission_negative.PO_NO,NVL(commission_negative.ryc,0) AS RYC"
					+ "					      FROM TSLI_GA_COMMISSION_negative commission_negative"
					+ "					      WHERE 	commission_negative.pe_no_mm = ?"
					+ "					              AND ( commission_negative.cont_mms BETWEEN 13 and 72 )"
					+ "					              AND commission_negative.pe_no_soli = commission_negative.pe_no_col "
					+ "					              AND TO_DATE(commission_negative.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')"
					+ "					      UNION ALL"
					+ "					      SELECT commission_negative.CLOS_YM,commission_negative.PE_NO_COL,commission_negative.PO_NO,0.5*NVL(commission_negative.ryc,0)  AS RYC"
					+ "					      FROM TSLI_GA_COMMISSION_negative commission_negative"
					+ "					      WHERE 	commission_negative.pe_no_mm = ?"
					+ "					              AND ( commission_negative.cont_mms BETWEEN 13 and 72 )"
					+ "					              AND commission_negative.pe_no_soli <> commission_negative.pe_no_col"
					+ "					              AND TO_DATE(commission_negative.CLOS_YM,'YYYYMM') BETWEEN TO_DATE('"+paYear+"01','YYYYMM') AND TO_DATE('"+paYear+"12','YYYYMM')";
			List<Map<String, Object>> result = db.queryList(vaSql,paPeNo,paPeNo);
		    if (result != null) {
		        return result;
		    } else {
		        return null;
		    }
		}
	 
}
